package test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class MysqlJDBC {

	/*
	 * 数据库连接信息
	 */
	String driverName = "com.mysql.jdbc.Driver";
	String dbURL = "jdbc:mysql://localhost:3306/ki?useUnicode=true&useSSL=false&characterEncoding=UTF-8";
	String userName = "root";
	String userPwd="root";
	
	/*
	 * 通用实例
	 */
	Connection dbConn = null;
	Statement stat = null;
	ResultSet rs = null;
	
	/**
	 * 连接数据库
	 */
	@Before
	public void befor(){
		
		try
		{
			Class.forName(driverName);
			System.out.println("加载驱动成功");
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("加载驱动失败");
		}
		try
		{
			 dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
			System.out.println("连接数据库成功！");
		}catch(Exception e)
		{
			e.printStackTrace();
			System.out.print("SQL Server连接失败");
		}
		
		try {
			stat = dbConn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("创建语句对象接口失败?");
		}

	}
	
	@Test
	public void testConnection() {
		System.out.println("连接成功！");
	}
	
	/**
	 * 查询示例，查询emp！！！
	 */
	@Test
	public void query(){
		
		String sql = "select * from t_emp;";
		try {
			rs = stat.executeQuery(sql);
			while(rs.next()){
				System.out.println("name:"+rs.getString("login_name"));
//				System.out.println("id:"+rs.getString("id"));
//				System.out.println("name:"+rs.getString("name"));
//				System.out.println("address:"+rs.getString("address"));
				System.out.println("---------------------------------------------------");
			}
			System.out.println("查询完成！！！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("结果集操作失败！");
		}
		
	}
	
	
	/**
	 * 查询很多数据示例，查询emp！！！
	 */
	@Test
	public void queryMore(){
		
		String sql = "select count(*) from emp;";
		try {
			long startTime = System.currentTimeMillis();
			rs = stat.executeQuery(sql);
			long endTime = System.currentTimeMillis();
			while(rs.next()) {
				int count = rs.getInt(1);
				System.out.println("共："+count+"条数据！！");
			}
			
			long executeTime = endTime - startTime;
			System.out.println("查询用时："+executeTime);
			
			System.out.println("查询完成！！！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("查询失败！！");
		}
		
	}
	
	
	
	/**
	 * 新增示例
	 */
	@Test
	public void add(){
		
		String sql = "insert into emp values('john','earth');";
		try {
			int count = stat.executeUpdate(sql);
			System.out.println("数据插入完成:"+count+"条数据受影响！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("插入数据失败！！！！！！");
		} 
	}
	
	/**
	 * 新增示例数据好多条
	 */
	@Test
	public void addMore(){
		String sql = "insert into emp (name,address) values(?,?);";
		int total = 0;
		
		try {
			dbConn.setAutoCommit(false);
			
			String name = "";
			String address = "";
			long startTime = System.currentTimeMillis();
			for(int i = 0;i<100000;i++) {
				PreparedStatement pstat = dbConn.prepareStatement(sql);
				name = "ki"+i;
				address ="earth"+i;
				pstat.setString(1, name);
				pstat.setString(2, address);
				int count = pstat.executeUpdate();
				total +=count;
			}
			dbConn.commit();
			long endTime = System.currentTimeMillis();
			long executeTime = endTime - startTime;
			System.out.println("插入用时："+executeTime+"ms");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("插入数据失败！！！！！！");
			try {
				dbConn.rollback();
				System.out.println("数据已回滚！");
			} catch (SQLException e1) {
				e1.printStackTrace();
				System.out.println("回滚失败！！！！！");
			}
		} 
		
		System.out.println("插入数据完成："+total+"条！！！！");
	}
	
	/**
	 * 新增示例数据好多条
	 */
	@Test
	public void addMoreExa(){
		String data = "北京市，天津市，上海市，重庆市，河北省，山西省，辽宁省，吉林省，黑龙江省，江苏省，浙江省，安徽省，福建省，江西省，山东省，河南省，湖北省，湖南省，广东省，海南省，四川省，贵州省，云南省，陕西省，甘肃省，青海省，台湾省，内蒙古自治区，广西壮族自治区，西藏自治区，宁夏回族自治区，新疆维吾尔自治区，香港特别行政区，澳门特别行政区";
		String[] dataArr = data.split("，");
		System.out.println(dataArr.length+":"+Arrays.toString(dataArr));
		String sql = "insert into province (name) values(?);";
		int total = 0;
		
		try {
			dbConn.setAutoCommit(false);
			
			String name = "";
			long startTime = System.currentTimeMillis();
			for(int i = 0;i<dataArr.length;i++) {
				PreparedStatement pstat = dbConn.prepareStatement(sql);
				name = dataArr[i];
				System.out.println(name);
				pstat.setString(1, name);
				int count = pstat.executeUpdate();
				total +=count;
			}
			dbConn.commit();
			long endTime = System.currentTimeMillis();
			long executeTime = endTime - startTime;
			System.out.println("插入用时："+executeTime+"ms");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("插入数据失败！！！！！！");
			try {
				dbConn.rollback();
				System.out.println("数据已回滚！");
			} catch (SQLException e1) {
				e1.printStackTrace();
				System.out.println("回滚失败！！！！！");
			}
		} 
		
		System.out.println("插入数据完成："+total+"条！！！！");
	}
	
	/**
	 * 更新示例
	 */
	@Test
	public void update(){
		String sql = "update emp set address='earth' where id = '1'";
		try {
			int count = stat.executeUpdate(sql);
			System.out.println("数据更新完成:"+count+"条数据受影响！！！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("更新数据失败！！！");
		} 
	}
	
	/**
	 * 删除示例
	 */
	@Test
	public void detele(){
		String sql = "delete from emp where id='2'";
		try {
			int count = stat.executeUpdate(sql);
			System.out.println("数据删除完成:"+count+"条数据受影响！！！");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("删除数据失败！！！");
		} 
	}
	
	@After
	public void after(){
		if(dbConn != null){
			try {
				dbConn.close();
				System.out.println("数据库连接已断开。");
			} catch (SQLException e) {
				System.out.println("SQL Server关闭连接失败！！");
				e.printStackTrace();
			}
		}
	}
	

}
